const dayjs = require('dayjs')
const db = require('../db/index')
const createExcel = require('../js/exceljs')
// 获取最近门店列表
exports.storelist = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let location = `POINT(${pageData.location})`
    // const sql = `select * from store where region like '%${city}%' and appid=? limit ${size} OFFSET  ${from}`
    const sql1 = `SELECT *,ST_Distance_Sphere(location2, ST_GeomFromText(?)) AS distance FROM store where appid=? and status=1 HAVING distance <= 100000 ORDER BY distance`
    db.query(sql1, [location, pageData.appid], (err, res2) => {
        console.log(res2)
        if (res2.length == 0) {
            const sql2 = `select * from store where appid=? and status=1 limit ${size} OFFSET  ${from}`
            db.query(sql2, [pageData.appid], (err, res3) => {
                res3.forEach((item) => {
                    delete item.password
                })
                res.send({
                    res: res3,
                })
            })
        } else {
            res.send({
                res: res2,
            })
        }
    })
    // db.query(sql, [pageData.appid], (err, res2) => {
    //     if (err) return res.cc(err)
    //     if (res2.length == 0) {
    //         const sql2 = `select * from store where appid=? limit ${size} OFFSET  ${from}`
    //         db.query(sql2, [pageData.appid], (err, res3) => {
    //             res3.forEach((item) => {
    //                 delete item.password
    //             })
    //             res.send({
    //                 res: res3,
    //             })
    //         })
    //     } else {
    //         res.send({
    //             res: res2,
    //         })
    //     }

    // })
}
// 获取门店详情
exports.storeDetils = (req, res) => {
    let data = req.body
    let sql = `select * from store where _id=?`
    db.query(sql, [data.storeId], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            res: res2[0],
        })
    })
}
// 获取用户ip
exports.getIp = (req, res) => {
    const ip = req.headers['x-forwarded-for'] || req.socket.remoteAddress
    res.send({
        res: ip
    })
}
// 获取门店列表
exports.getstorelist = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from store where appid=? and status=? limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=? and status=? `
        db.query(sql2, [appid, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取所有门店列表
exports.getstorelistAll = (req, res) => {
    let pageData = req.body
    let appid = pageData.appid
    const sql = `select * from store where appid=? and status=?`
    db.query(sql, [appid, pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=? and status=? `
        db.query(sql2, [appid, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 小程序搜索门店列表
exports.storeAuditSerachSamll = (req, res) => {
    let pageData = req.body
    const sql = `select * from store where appid=${pageData.appid}  and (name like "%${pageData.serchData}%" or linkman like "%${pageData.serchData}%" or phone like "%${pageData.serchData}%")`
    db.query(sql, (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            res: res2,
        })
    })
}
// 获取门店列表
exports.getstorelist2 = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from store where appid=?  limit ${size} OFFSET  ${from}`
    db.query(sql, [appid], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=?`
        db.query(sql2, [appid, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取门店待审核列表小程序
exports.getstorelist2Samll = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from store where appid=? and status=? and region like "%${pageData.region}%"  limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=? and status=? and region like "%${pageData.region}%" `
        db.query(sql2, [appid, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取门店待审核列表小程序搜索
exports.getstorelist2SamllSeach = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from store where appid=? and status=? and (linkman like "%${pageData.serchData}%" or name like "%${pageData.serchData}%" or phone like "%${pageData.serchData}%") and region like "%${pageData.region}%"  limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=? and status=? and (linkman like "%${pageData.serchData}%" or name like "%${pageData.serchData}%" or phone like "%${pageData.serchData}%") and region like "%${pageData.region}%"`
        db.query(sql2, [appid, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 搜索
exports.serachStoreList = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from store where appid=? and status=1 and name like "%${pageData.name}%" limit ${size} OFFSET  ${from}`
    db.query(sql, [appid], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=? and status=1 and name like "%${pageData.name}%"`
        db.query(sql2, [appid], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })
    })
}
// 门店审核条件查询
exports.storeAuditSerach = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    pageData.name = pageData.name ? pageData.name : ''
    pageData.linkman = pageData.linkman ? pageData.linkman : ''
    pageData.region = pageData.region ? pageData.region : ''
    pageData.phone = pageData.phone ? pageData.phone : ''
    pageData.account = pageData.account ? pageData.account : ''
    pageData.status = pageData.status?.length > 0 ? pageData.status : ['0', '1', '2']
    const sql = `select * from store where appid=${appid}  and (name like "%${pageData.name}%" and linkman like "%${pageData.linkman}%" and region like "%${pageData.region}%" and phone like "%${pageData.phone}%" and account like "%${pageData.account}%") and status in (?) limit ${size} OFFSET  ${from}`
    db.query(sql, [pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from store where appid=? and (name like "%${pageData.name}%" and linkman like "%${pageData.linkman}%" and region like "%${pageData.region}%" and phone like "%${pageData.phone}%" and account like "%${pageData.account}%") and status in (?)`
        db.query(sql2, [appid, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })
    })
}
// 删除
exports.storeDelete = (req, res) => {
    let id = req.query.id
    const sql = `delete from store where _id=?`
    db.query(sql, id, (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: '删除成功'
        })
    })
}
// 修改
exports.storeChange = (req, res) => {
    let data = req.body
    if (Array.isArray(data.region)) {
        data.region = JSON.stringify(data.region)
    }
    const sql = `update store set status=?, head=?,linkman=?,name=?,phone=?,password=?,region=?,type=?,crossDomain=? where _id=?  `
    db.query(sql, [data.status, data.head, data.linkman, data.name, data.phone, data.password, data.region, data.type, data.crossDomain, data._id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            messge: '修改成功'
        })
    })
}
// 修改门店小程序
exports.storeChangeSamll = (req, res) => {
    let data = req.body
    for (const key in data) {
        if (Array.isArray(data[key]) || data[key] instanceof Object) {
            data[key] = JSON.stringify(data[key])
        }
    }
    let location2 = `POINT(${JSON.parse(data.location2).x} ${JSON.parse(data.location2).y})`
    delete data.location2
    const sql = `update store set ? where _id=?`
    let getStore = `select * from store where  _id=? `
    const sqlLocation = `update store set location2 = ST_PointFromText(?) where _id= ?;`
    db.query(sql, [data, data._id], (err, res2) => {
        if (err) return res.cc(err)
        db.query(sqlLocation, [location2, data._id], (err, res4) => {
            if (err) return res.cc(err)
            db.query(getStore, [data._id], (err, res3) => {
                delete res3[0].password
                if (err) return res.cc(err)
                res.send({
                    messge: '修改成功',
                    res: res3[0]
                })
            })
        })


    })
}
// 获取门店库存膜列表
exports.storeInventory = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from deliver where appid=? and storeId=? limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from deliver where appid=? and storeId=?`
        db.query(sql2, [appid, pageData.storeId], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取门店未使用的库存
exports.storeInventoryUnUse = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from deliver where appid=? and storeId=? and useCount=0 and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%") limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId, pageData.deliverStatus], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from deliver where appid=? and storeId=? and useCount=0 and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%")`
        db.query(sql2, [appid, pageData.storeId, pageData.deliverStatus], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取门店已经使用的库存
exports.storeInventoryUse = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from deliver where  useCount > 0 and count > 0 and appid=? and storeId=? and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%")  limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId, pageData.deliverStatus], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from deliver where useCount > 0 and count > 0 and appid=? and storeId=? and deliverStatus=?  and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%")`
        db.query(sql2, [appid, pageData.storeId, pageData.deliverStatus], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取门店使用完的库存
exports.storeInventoryUseAll = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from deliver where appid=? and storeId=? and count=0  and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%") limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId, pageData.deliverStatus], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from deliver where appid=? and storeId=? and count=0  and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%")`
        db.query(sql2, [appid, pageData.storeId, pageData.deliverStatus], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取库存详情
exports.storeInventoryDetils = (req, res) => {
    let pageData = req.body
    const sql = `select * from deliver where appid=? and _id=? `
    db.query(sql, [pageData.appid, pageData.id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            res: res2[0],
        })
    })
}
// 根据质保信息查询膜信息小程序
exports.storeInventorySmall = (req, res) => {
    let pageData = req.body
    let appid = pageData.appid
    const sql = `select * from deliver where appid=? and storeId=? and filmNo=? and storeName=? and deliverStatus = 1`
    db.query(sql, [appid, pageData.storeId, pageData.filmNo, pageData.storeName], (err, res2) => {
        if (err) return res.cc(err)
        if (res2.length > 0) {
            res.send({
                res: res2[0],
                message: '查询成功',
                type: 1
            })
        } else {
            res.send({
                message: '未找到此编号',
                type: 0
            })
        }

    })
}
// 添加发货库存
exports.storeInventoryAdd = (req, res) => {
    let data = req.body
    try {
        let start = data.startNumber.slice(-3) * 1
        let end = data.endNumber.slice(-3) * 1
        let numberStr = data.startNumber.slice(0, -3)
        if (start > end) {
            res.send({
                message: '添加失败',
                type: 1
            })
        }
        data.filmNo = []
        for (let i = 1; i <= (end + 1 - start); i++) {
            if (i < 10) {
                data.filmNo.push(`${numberStr}00${i}`)
            } else if (i < 100) {
                data.filmNo.push(`${numberStr}0${i}`)
            } else {
                data.filmNo.push(`${numberStr}${i}`)
            }
        }
        data.filmNo.forEach((item) => {
            data.storeId.forEach((item2) => {
                let setData = { ...data }
                setData.storeId = item2
                setData.filmNo = item
                setData.deliverStatus = 0
                setData.number = 1
                delete setData.startNumber
                delete setData.endNumber
                const selectStore = `select * from store where  _id=?`
                db.query(selectStore, [item2], (err, res3) => {
                    if (err) return res.cc(err)
                    setData.storeName = res3[0].name
                    setData.linkman = res3[0].linkman
                    setData.storePhone = res3[0].phone
                    const selectFilm = `select * from film where _id=?`
                    db.query(selectFilm, [setData.filmId], (err, res4) => {
                        if (err) return res.cc(err)
                        setData.filmName = res4[0].name
                        const sql = `insert into deliver set ?`
                        db.query(sql, [setData], (err, res2) => {
                            if (err) return res.cc(err)

                        })
                    })

                })

            })
        })
        res.send({
            message: '添加成功',
            type: 1
        })
    } catch (error) {
        res.send({
            message: '添加失败',
            type: 2
        })
    }

}

// 添加发货库存表格发货
exports.storeInventoryAdd2 = (req, res) => {
    let data = req.body
    let fimSql = `select * from film where nameNumber=?`
    let num = 0
    try {

        new Promise((resolve, reject) => {
            data.table.forEach((item) => {
                db.query(fimSql, [item.filmType], (err, res4) => {
                    if (res4.length == 0) {
                        reject(`${item.code}没有此型号`)
                    } else {
                        num++
                        if (num == data.table.length) {
                            resolve('成功')
                        }
                    }
                })
            })
        }).then((resdata) => {

            data.table.forEach((item) => {
                db.query(fimSql, [item.filmType], (err, res4) => {
                    console.log(res4)
                    if (res4.length > 0) {
                        let setData = {}
                        setData.storeId = data.storeId
                        setData.filmNo = item.code
                        setData.appid = data.appid
                        setData.lacquerNumber = res4[0].lacquerNumber
                        setData.colorAlterationNumber = res4[0].colorAlterationNumber
                        setData.roofNumber = res4[0].roofNumber
                        setData.sunNumber = res4[0].sunNumber
                        setData.deliverStatus = 0
                        setData.filmType = res4[0].type
                        setData.count = res4[0].countAll
                        setData.filmAge = res4[0].age
                        setData.customerService = data.customerService
                        setData.number = 1
                        setData.filmId = res4[0]._id

                        const selectStore = `select * from store where  _id=?`
                        db.query(selectStore, [data.storeId], (err, res3) => {
                            if (err) return res.cc(err)
                            setData.storeName = res3[0].name
                            setData.linkman = res3[0].linkman
                            setData.storePhone = res3[0].phone
                            const selectFilm = `select * from film where _id=?`
                            db.query(selectFilm, [setData.filmId], (err, res5) => {
                                if (err) return res.cc(err)
                                if (res5.length == 0) {

                                    reject(`没有此膜${setData.filmNo}`)
                                } else {
                                    setData.filmName = res5[0].name
                                    const sql = `insert into deliver set ?`
                                    db.query(sql, [setData], (err, res2) => {
                                        if (err) return res.cc(err)

                                    })
                                }

                            })

                        })
                    }
                })
            })
            res.send({
                message: '添加成功',
                type: 1
            })
        }).catch(resErr => {
            res.send({
                message: resErr,
                type: 2
            })
        })

    } catch (error) {
        res.send({
            message: error ? error : '添加失败',
            type: 2
        })
    }

}

// 添加发货库存小程序
exports.storeInventoryAddSamll = (req, res) => {
    let data = req.body
    try {
        let start = data.startNumber.slice(-2) * 1
        let end = data.endNumber.slice(-2) * 1
        let numberStr = data.startNumber.slice(0, -2)
        data.filmNo = []
        for (let i = 1; i <= (end + 1 - start); i++) {
            if (i < 10) {
                data.filmNo.push(`${numberStr}0${i}`)
            } else {
                data.filmNo.push(`${numberStr}${i}`)
            }
        }
        let deliverSql = `select * from deliver where appid=? and storeId=? and filmNo in(?)`
        db.query(deliverSql, [data.appid, data.deliverStoreId, data.filmNo], (err, res5) => {
            if (res5.length > 0) {
                data.filmNo.forEach((item) => {
                    let setData = { ...data }
                    setData.filmNo = item
                    delete setData.startNumber
                    delete setData.endNumber
                    const selectStore = `select * from store where  _id=?`
                    db.query(selectStore, [setData.storeId], (err, res3) => {
                        if (err) return res.cc(err)
                        setData.storeName = res3[0].name
                        setData.linkman = res3[0].linkman
                        setData.phone = res3[0].phone
                        const selectFilm = `select * from film where _id=?`
                        db.query(selectFilm, [setData.filmId], (err, res4) => {
                            if (err) return res.cc(err)
                            setData.filmName = res4[0].name
                            const sql = `insert into deliver set ?`
                            db.query(sql, [setData], (err, res2) => {
                                if (err) return res.cc(err)
                            })
                        })

                    })

                })
                res.send({
                    message: '添加成功',
                    type: 1
                })

            } else {
                res.send({
                    message: '未找到本店有此质保编号',
                    type: 0
                })
                // throw '未找到本店有此质保编号'
            }
        })

    } catch (error) {
        res.send({
            message: '添加失败',
            type: 2
        })
    }

}
// 条件查询
exports.storeInventorySerach = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    pageData.filmNo = pageData.filmNo ? pageData.filmNo : ''
    pageData.deliverStorePhone = pageData.deliverStorePhone ? pageData.deliverStorePhone : ''
    pageData.filmType = pageData.filmType ? pageData.filmType : ''
    pageData.filmName = pageData.filmName ? pageData.filmName : ''
    pageData.status = pageData.status?.length > 0 ? pageData.status : ['0', '1', '2']
    // let sql = ''
    // if (pageData.status?.length==0) {
    //     pageData.status = ['已使用', '未使用', '已用完']
    //     sql = `select * from deliver where appid=${appid}  and storeId=${pageData.storeId} and (filmNo like "%${pageData.filmNo}%" and deliverStorePhone like "%${pageData.deliverStorePhone}%" and filmType like "%${pageData.filmType}%" and filmName like "%${pageData.filmName}%") and status in (?) limit ${size} OFFSET  ${from}`
    // } else {
    //     sql = `select * from deliver where appid=${appid}  and storeId=${pageData.storeId} and (filmNo like "%${pageData.filmNo}%" and deliverStorePhone like "%${pageData.deliverStorePhone}%" and filmType like "%${pageData.filmType}%" and filmName like "%${pageData.filmName}%") limit ${size} OFFSET  ${from}`
    // }
    const sql = `select * from deliver where appid=${appid}  and storeId=${pageData.storeId} and (filmNo like "%${pageData.filmNo}%" and deliverStorePhone like "%${pageData.deliverStorePhone}%" and filmType like "%${pageData.filmType}%" and filmName like "%${pageData.filmName}%") and status in (?) limit ${size} OFFSET  ${from}`
    db.query(sql, [pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from deliver where appid=? and storeId=${pageData.storeId} and (filmNo like "%${pageData.filmNo}%" and deliverStorePhone like "%${pageData.deliverStorePhone}%" and filmType like "%${pageData.filmType}%" and filmName like "%${pageData.filmName}%")`
        db.query(sql2, [appid], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })
    })
}
// 修改使用次数
exports.changeCount = (req, res) => {
    let data = req.body
    const selectSql = `select * from deliver where _id=?`
    let upSql = ``
    db.query(selectSql, [data._id], (err, res2) => {
        if (err) return res.cc(err)
        if (res2[0].useCount * 1 < data.count * 1) {
            data.status = '已使用'
        } else if (res2[0].useCount * 1 == data.count * 1) {
            data.status = '已用完'
        } else if (res2[0].useCount * 1 == 0) {
            data.status = '未使用'
        }
        upSql = `update deliver set count=?,status=? where _id=? `
        db.query(upSql, [data.count, data.status, data._id], (err, res3) => {
            if (err) return res.cc(err)
            res.send({
                message: '修改完成'
            })
        })
    })
}
// 删除库存
exports.storeInventoryDelete = (req, res) => {
    let id = req.query.id
    let sql = `delete from deliver where _id=?`
    db.query(sql, id, (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: '删除完成'
        })
    })
}
// 导出表格
exports.storeInventoryCreateExcel = (req, res) => {
    let data = req.body
    columns = [
        { header: '收货时间', key: 'timeOfReceipt' },
        { header: '产品类型', key: 'filmType' },
        { header: '产品名称', key: 'filmName' },
        { header: '发货门店', key: 'deliverStoreName' },
        { header: '质保编号', key: 'filmNo' },
        { header: '质保年限', key: 'filmAge' },
        { header: '总贴膜次数', key: 'count' },
        { header: '状态', key: 'status' },
        { header: '已使用次数', key: 'useCount' },
        { header: '未使用次数', key: 'unUseCount' }
    ]
    let sql = ``
    if (data.id == 'all') {
        sql = 'select * from deliver where storeId=?'
    } else {
        sql = 'select * from deliver where storeId=? and _id in (?)'
    }


    db.query(sql, [data.storeId, data.id], async (err, res2) => {
        if (err) return res.cc(err)
        const buffer = await createExcel.createExcel(columns, res2)
        res.setHeader('Content-disposition', 'attachment; filename=file.xlsx')
        res.setHeader('Content-type', 'application/octet-stream')
        res.send(buffer)
    })

}
// 获取收货列表详情
exports.storeReceiveList = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    const sql = `select * from deliver where appid=? and storeId=? and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%") limit ${size} OFFSET  ${from}`
    db.query(sql, [pageData.appid, pageData.storeId, pageData.deliverStatus], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            res: res2,
        })
    })
}
// 修改收货状态
exports.storeReceivechangeStatus = (req, res) => {
    let pageData = req.body
    const sql = `update deliver set deliverStatus=? where _id=?`
    db.query(sql, [pageData.deliverStatus, pageData.id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: '修改成功'
        })
    })
}
// 门店发货列表
exports.storeDeliverList = (req, res) => {
    let pageData = req.body
    let sql = `select * from deliver where appid=? and deliverStoreId=? and deliverStatus=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or filmType like "%${pageData.serchData}%")`
    db.query(sql, [pageData.appid, pageData.storeId, pageData.deliverStatus], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            res: res2
        })
    })
}
// 获取门店质保列表
exports.storeQualityList = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from quality where appid=? and storeId=?  limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from quality where appid=? and storeId=?`
        db.query(sql2, [appid, pageData.storeId], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取门店质保列小程序
exports.storeQualityListSamll = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from quality where appid=? and storeId=? and status=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or ownerName like "%${pageData.serchData}%"  or ownerPhone like "%${pageData.serchData}%") limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId, pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from quality where appid=? and storeId=? and status=? and (filmNo like "%${pageData.serchData}%" or filmName like "%${pageData.serchData}%" or ownerName like "%${pageData.serchData}%"  or ownerPhone like "%${pageData.serchData}%")`
        db.query(sql2, [appid, pageData.storeId, pageData.status], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 修改门店质保信息
exports.storeQualityChange = (req, res) => {
    let data = req.body
    data.imgList = JSON.stringify(data.imgList)
    data.timeOfReceipt = JSON.stringify(data.timeOfReceipt)
    let sql = `update quality set carApart=?,carName=?,carNo=?,carVIN=?,filmAge=?,filmName=?,filmNo=?,filmType=?,imgList=?,ownerName=?,ownerPhone=?,price=?,remark=?,status=?,storeId=?,storeWork=?,storeWorkPhone=?,workTime=?,timeOfReceipt=? where _id=? `
    db.query(sql, [data.carApart, data.carName, data.carNo, data.carVIN, data.filmAge, data.filmName, data.filmNo, data.filmType, data.imgList, data.ownerName, data.ownerPhone, data.price, data.remark, data.status, data.storeId, data.storeWork, data.storeWorkPhone, data.workTime, data.timeOfReceipt, data._id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: "修改成功"
        })
    })
}
// 删除门店质保
exports.storeQualityDelete = (req, res) => {
    let id = req.query.id
    let sql = `delete from quality where _id=?`
    db.query(sql, [id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: "删除成功"
        })
    })
}
// 导出质保表格
exports.storeQualityCreateExcel = (req, res) => {
    let data = req.body
    columns = [
        { header: '质保上传时间', key: 'workTime' },
        { header: '产品类型', key: 'filmType' },
        { header: '产品名称', key: 'filmName' },
        { header: '施工门店', key: 'storeWork' },
        { header: '车主姓名', key: 'ownerName' },
        { header: '联系方式', key: 'ownerPhone' },
        { header: '车牌号', key: 'carNo' },
        { header: '车架号', key: 'carVIN' },
        { header: '施工部位', key: 'carApart' },
        { header: '质保年限', key: 'filmAge' }
    ]
    let sql = ``
    if (data.id == 'all') {
        sql = 'select * from quality where storeId=?'
    } else {
        sql = 'select * from quality where storeId=? and _id in (?)'
    }


    db.query(sql, [data.storeId, data.id], async (err, res2) => {
        if (err) return res.cc(err)
        const buffer = await createExcel.createExcel(columns, res2)
        res.setHeader('Content-disposition', 'attachment; filename=download.xlsx')
        res.setHeader('Content-type', 'application/octet-stream')
        res.send(buffer)
    })

}
// 条件门店质保查询
exports.storeQualitySerach = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    pageData.ownerName = pageData.ownerName ? pageData.ownerName : ''
    pageData.ownerPhone = pageData.ownerPhone ? pageData.ownerPhone : ''
    pageData.filmNo = pageData.filmNo ? pageData.filmNo : ''
    pageData.carVIN = pageData.carVIN ? pageData.carVIN : ''
    pageData.workTime = pageData.workTime ? dayjs(pageData.workTime).format('YYYY-MM-DD HH:mm:ss') : ''
    pageData.filmType = pageData.filmType ? pageData.filmType : ''
    pageData.carName = pageData.carName ? pageData.carName : ''
    const sql = `select * from quality where appid=${appid}  and storeId=${pageData.storeId} and (ownerName like "%${pageData.ownerName}%" and ownerPhone like "%${pageData.ownerPhone}%" and filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and workTime like "%${pageData.workTime}%" and filmType like "%${pageData.filmType}%" and carName like "%${pageData.carName}%")  limit ${size} OFFSET  ${from}`
    db.query(sql, [pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from quality where appid=? and storeId=${pageData.storeId} and (ownerName like "%${pageData.ownerName}%" and ownerPhone like "%${pageData.ownerPhone}%" and filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and workTime like "%${pageData.workTime}%" and filmType like "%${pageData.filmType}%" and carName like "%${pageData.carName}%")`
        db.query(sql2, [appid], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })
    })
}

// 获取预约列表
exports.storePreorderedList = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    const sql = `select * from preordered where appid=? and storeId=? limit ${size} OFFSET  ${from}`
    db.query(sql, [appid, pageData.storeId], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from preordered where appid=? and storeId=?`
        db.query(sql2, [appid, pageData.storeId], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })


    })
}
// 获取预约详情
exports.storePreorderedDetils = (req, res) => {
    let data = req.body
    let sql = `select * from preordered where _id=?`
    db.query(sql, [data._id], (err, res2) => {
        if (err) return res.cc(err)
        let sql = `select * from store where _id=?`
        db.query(sql, [data.storeId], (err, res3) => {
            if (err) return res.cc(err)
            res.send({
                res: {
                    preordered: res2[0],
                    store: res3[0]
                }
            })
        })
    })
}
// 删除预约
exports.storePreorderedDelete = (req, res) => {
    let data = req.body
    let sql = `delete from preordered where _id=?`
    db.query(sql, [data._id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: '删除成功'
        })
    })
}
// 修改预约状态
exports.storePreorderedStatus = (req, res) => {
    let data = req.body
    let sql = `update preordered set status=? where _id=?`
    db.query(sql, [data.status, data._id], (err, res2) => {
        if (err) return res.cc(err)
        res.send({
            message: '修改成功'
        })
    })
}
// 导出预约表格
exports.storePreorderedCreateExcel = (req, res) => {
    let data = req.body
    columns = [
        { header: '预约时间', key: 'time' },
        { header: '客户姓名', key: 'name' },
        { header: '联系方式', key: 'phone' },
        { header: '车辆品牌', key: 'carName' },
        { header: '预约状态', key: 'status' },
        { header: '备注', key: 'remark' },
        { header: '车牌号', key: 'carNo' },
        { header: '车架号', key: 'carVIN' },
        { header: '膜品类型', key: 'filmType' },
        { header: '膜品型号', key: 'filmName' }
    ]
    let sql = ``
    if (data.id == 'all') {
        sql = 'select * from preordered where storeId=?'
    } else {
        sql = 'select * from preordered where storeId=? and _id in (?)'
    }


    db.query(sql, [data.storeId, data.id], async (err, res2) => {
        if (err) return res.cc(err)
        res2.forEach((item) => {
            item.status = item.status == 0 ? '已预约' : item.status == 1 ? '预约完成' : item.status == 2 ? '预约取消' : ''
        })
        const buffer = await createExcel.createExcel(columns, res2)
        res.setHeader('Content-disposition', 'attachment; filename=download.xlsx')
        res.setHeader('Content-type', 'application/octet-stream')
        res.send(buffer)
    })

}
// 条件预约查询
exports.storePreorderedSerach = (req, res) => {
    let pageData = req.body
    let page = pageData.page * 1 - 1
    let size = pageData.size * 1
    let from = page * size
    let appid = pageData.appid
    pageData.name = pageData.name ? pageData.name : ''
    pageData.phone = pageData.phone ? pageData.phone : ''
    pageData.carName = pageData.carName ? pageData.carName : ''
    pageData.carType = pageData.carType ? pageData.carType : ''
    pageData.carNo = pageData.carNo ? pageData.carNo : ''
    pageData.status = pageData.status?.length > 0 ? pageData.status : ['0', '1', '2']
    const sql = `select * from preordered where appid=${appid}  and storeId=${pageData.storeId} and (name like "%${pageData.name}%" and phone like "%${pageData.phone}%" and carName like "%${pageData.carName}%" and carType like "%${pageData.carType}%" and carNo like "%${pageData.carNo}%") and status in (?) limit ${size} OFFSET  ${from}`
    db.query(sql, [pageData.status], (err, res2) => {
        if (err) return res.cc(err)
        let sql2 = `select count(*) from preordered where appid=? and storeId=${pageData.storeId} and (name like "%${pageData.name}%" and phone like "%${pageData.phone}%" and carName like "%${pageData.carName}%" and carType like "%${pageData.carType}%" and carNo like "%${pageData.carNo}%")`
        db.query(sql2, [appid], (err2, res3) => {
            if (err2) return res.cc(err2)
            res.send({
                res: res2,
                total: res3[0]['count(*)']
            })
        })
    })
}